﻿using Freshbox2._0.BLL;
using Freshbox2._0.MODEL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

using System.Web;
using System.Web.Mvc;
using PagedList;
using System.Text;
using Freshbox2._0.COMMON;

namespace Freshbox2._0.UI.Controllers
{
    public class OrderStatisticController : BaseController<usersorder>
    {

        // GET: /OrderStatistic/
        protected admin CurrentUer
        {
            get { return Session["CurrentUer"] == null ? null : (admin)Session["CurrentUer"]; }
        }

        public override ActionResult Index()
        {
            return View();
        }

        public override BLL.BaseBll<usersorder> bll
        {
            get
            {
                return new UsersOrderBll();
            }
        }
        public class StoreOnNum
        {
            public int num { get; set; }
        }

        public class OrderModel
        {
            public int userCode;
            public string userName;
            public string orderCode;
            public string orderTime;
            public decimal orderSumprice;
            public string wechatName;
            public string userPhone;
            public DateTime time;
            public string orderPay; //支付方式
            public string preferentialMoney;//优惠金额
            public string storeName;//消费店铺
            public string isRepeat;//是否复购
            public string pindentStatus;  //煎饼订单状态
            public string consumType;//消费类型  线上 OR  线下
            public int consumTypeID;  //用于查询订单详情时区分不同的表          
        }

        List<OrderModel> OrderList = new List<OrderModel>();

        List<OrderModel> OrderListFinal = new List<OrderModel>();

        UserBll userbll = new UserBll();

        WaresBll waresBll = new WaresBll();

        StoreBll storeBll = new StoreBll();

        OrderDetailsBll orderDetailBll = new OrderDetailsBll();

        CouponBll couponBll = new CouponBll();

        PresellIndentBll indentBll = new PresellIndentBll();

        /// <summary>
        /// 获取店铺列表
        /// </summary>
        /// <returns></returns>  

        public ActionResult GetShopName()
        {
            return Json(storeBll.Search(x=>x.storeStatus==1).Select(x => new { shopID = x.storeid, shopName = x.storeName }).ToList());
        }

        /// <summary>
        /// 订单详细信息
        /// </summary>
        public class ListOrderDetail
        {
            public string orderCode;
            public string waresCode;
            public string waresName;
            public int waresNumber;
            public decimal waresPrice;
            public decimal TotalPrice;
            public string standard;
        }

        WaresBll wareBll = new WaresBll();

        OrderDetailsBll orderDetailsBll = new OrderDetailsBll();
        PwaresStandardBll standardBll = new PwaresStandardBll();    
        PresellIndentDetailBll indentDetailsBll = new PresellIndentDetailBll();
        PresellWaresBll presellwareBll = new PresellWaresBll();

        //查询订单详细信息  查看每条订单对应的详细信息 

        public ActionResult GetDetailByID(string orderCode, string date1, string date2, int consumTypeID)
        {
            var listOrderDetail = new List<ListOrderDetail>();

            if (consumTypeID == 2)
            {
                var list = orderDetailsBll.Search(x => x.orderCode == orderCode).ToList().Select(x => new { x.orderCode, x.waresCode, x.waresQuantity }).ToList();

                foreach (var oneDetailCode in list)
                {
                    //查询是否有优惠券
                    ListOrderDetail detail = new ListOrderDetail();
                    var listCoupon = couponBll.Search(x => x.couponId == oneDetailCode.waresCode).Select(x => new { x.couponId, x.couponValue, }).ToList();
                    if (listCoupon.Count != 0)
                    {
                        detail.orderCode = oneDetailCode.orderCode;
                        detail.waresCode = oneDetailCode.waresCode;
                        detail.waresName = "优惠券";
                        detail.waresNumber = 1;
                        detail.waresPrice = Convert.ToDecimal(listCoupon.FirstOrDefault().couponValue);
                        detail.TotalPrice = Convert.ToDecimal(listCoupon.FirstOrDefault().couponValue * 1);//总价
                        listOrderDetail.Add(detail);
                    }
                    var list2 = wareBll.Search(x => x.waresCode == oneDetailCode.waresCode).Select(x => new { x.waresCode, x.waresName, x.waresPrice }).ToList();
                    if (list2.Count != 0)
                    {
                        detail.orderCode = oneDetailCode.orderCode;
                        detail.waresCode = oneDetailCode.waresCode;
                        detail.waresName = list2.FirstOrDefault().waresName;
                        detail.waresNumber = Convert.ToInt32(oneDetailCode.waresQuantity);
                        detail.waresPrice = Convert.ToDecimal(list2.FirstOrDefault().waresPrice);
                        detail.TotalPrice = Convert.ToDecimal(list2.FirstOrDefault().waresPrice * oneDetailCode.waresQuantity);
                        listOrderDetail.Add(detail);
                    }
                }
            }
            //
            if (consumTypeID == 1)
            {
                var list = indentDetailsBll.Search(x => x.pindentId == orderCode).Select(x => new { x.pindentId, x.pwaresId, x.pwaresNum, x.pwaresStandardId, }).ToList();
                foreach (var oneDetailCode in list)
                {

                    ListOrderDetail detail = new ListOrderDetail();

                    var list2 = presellwareBll.Search(x => x.pwaresId == oneDetailCode.pwaresId).Select(x => new { x.pwaresId, x.pwaresName, x.pwaresPrice, x.pwaresNum }).ToList();
                    if (list2.Count != 0)
                    {
                        //var a = standardBll.Search(y => y.pwaresStandardId == oneDetailCode.pwaresStandardId).Select(x => new { x.pwaresStandardId, x.pstandardName }).ToList();
                        //if (a.Count != 0)
                        //{
                        //    detail.standard = a.FirstOrDefault().pstandardName;
                        //}
                        //else
                        //{
                        //    detail.standard = "";
                        //}

                        detail.waresCode = oneDetailCode.pwaresId.ToString();
                        detail.waresName = list2.FirstOrDefault().pwaresName;
                        detail.waresNumber = Convert.ToInt32(oneDetailCode.pwaresNum);
                        detail.waresPrice = Convert.ToDecimal(list2.FirstOrDefault().pwaresPrice);
                        detail.TotalPrice = Convert.ToDecimal(list2.FirstOrDefault().pwaresPrice * oneDetailCode.pwaresNum);
                        listOrderDetail.Add(detail);
                    }
                }
            }

            return Json(listOrderDetail);
        }

        public decimal Shouldmoney = 0.00m;//应收金额 

        public decimal Discountmoney = 0.00m;//优惠金额

        public decimal Actualmoney = 0.00m;//实际收入金额

        public decimal PerPrice; //客单价 

        public int OrderSum;  //客单量  PerPrice OrderSum

        //店铺的
        public decimal Shouldmoney1 = 0.00m;//应收金额 

        public decimal Discountmoney1 = 0.00m;//优惠金额

        public decimal Actualmoney1 = 0.00m;//实际收入金额

        public decimal PerPrice1; //客单价

        public int OrderSum1;  //客单量  PerPrice OrderSum

        //统计  总的营业额
        public ActionResult GetOrderAllStore(string storeid, string date1, string date2, string adminUsername, string userPhone)
        {
            var where = PredicateBuilder.True<usersorder>();
            StringBuilder sql=new StringBuilder();
            sql.Append(" select  COUNT(*)  as num   from(SELECT  COUNT(*) FROM usersorder WHERE  ");
            //默认查询本天的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid == "")
            {
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append("orderTime BETWEEN '" + midnight + "' and '" + todayNow + "'  GROUP BY storeid ) AS a  ");
                where = where.And(x => x.orderStatus == 1 && x.orderTime <= todayNow && x.orderTime >= midnight);
            }
            //根据日期查询  先查找出满足时间的条件  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "")
            {
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                int storeID = Convert.ToInt32(storeid);
                where = where.And(x => x.orderStatus == 1 && x.orderTime <= todayNow && x.orderTime >= midnight && x.storeid == storeID);
                sql.Append("orderTime BETWEEN '" + midnight + "' and '" + todayNow + "'  GROUP BY storeid ) AS a  ");
                //condition = x => x.orderStatus == 1 && x.orderTime <= todayNow && x.orderTime >= midnight && x.storeid == storeID;
            }
            //根据店铺查询
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && String.IsNullOrEmpty(storeid))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("orderTime BETWEEN '" + datetime1 + "' and '" + datetime2 + "'  GROUP BY storeid ) AS a  ");
                where = where.And(x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2);
            }
            //根据时间和店铺查询
            if (date1 != "" && date2 != "" && storeid != "")
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                int storeID = Convert.ToInt32(storeid);
                sql.Append("orderTime BETWEEN '" + datetime1 + "' and '" + datetime2 + "'  GROUP BY storeid ) AS a  ");
                where = where.And(x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2 && x.storeid == storeID);
            }
            //根据订单 计算 营业店铺数量
            var list = bll.SearchBySQL<StoreOnNum>(sql.ToString()).ToList();
            int storeOnNum = list.FirstOrDefault().num;

            // 目的 为计算订单量
            var a = bll.Search().Where(where.Compile());

            //计算总的营业额
            var listOrder = bll.Search().Where(where.Compile()).GroupBy(x => x.orderStatus).Select(x => new { orderSumprice = x.Sum(y => y.orderSumprice), discountMoney = x.Sum(y => y.discountMoney) }).AsQueryable();

            foreach (var oneOrder in listOrder)
            {
                if (string.IsNullOrEmpty(adminUsername) && string.IsNullOrEmpty(userPhone))
                {
                    OrderModel orderModel = new OrderModel();
                    orderModel.orderSumprice = Convert.ToDecimal(oneOrder.orderSumprice);
                    orderModel.preferentialMoney = oneOrder.discountMoney.ToString();
                    Actualmoney = Actualmoney + Convert.ToDecimal(orderModel.orderSumprice);
                    Discountmoney = Discountmoney + Convert.ToDecimal(orderModel.preferentialMoney);
                    Shouldmoney = Actualmoney + Discountmoney;
                    OrderSum = a.Count();
                    PerPrice = Shouldmoney / OrderSum;
                }
            } 
            return Json(new { rows = OrderList, storeOnNum = storeOnNum,Shouldmoney = Shouldmoney, OrderSum = OrderSum, PerPrice = PerPrice.ToString("#0.00"), Discountmoney = Discountmoney.ToString("#0.00"), Actualmoney = Actualmoney.ToString("#0.00") }, JsonRequestBehavior.AllowGet);
        }
        

        //使用SQL语句进行优化  

        //查询订单列表

        public ActionResult GetOrderBySql(string date1, string date2, string adminUsername, string userPhone, string storeid, string type, int page, int rows)
        {
            int count = 0;
            var orderbyList = new List<OrderModel>();
            StringBuilder sql = new StringBuilder();
            sql.Append("select usersorder.*,`user`.wechatName,userPhone,store.storeName  from usersorder  LEFT JOIN  `user` on usersorder.userCode=user.userCode INNER JOIN  store on store.storeid=usersorder.storeid WHERE orderStatus=1 ");
            //默认情况   默认查询本天的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append(" and orderTime between  '" + midnight + "' AND '" + todayNow + "' ");
            }
            //只按照店铺查询 默认是今天某店铺的订单情况   
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append(" and orderTime between  '" + midnight + "' AND '" + todayNow + "' and usersorder.storeid='" + shopid + "' ");
            }
            //按照时间和店铺查询  
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append(" and orderTime between  '" + datetime1 + "' AND '" + datetime2 + "' and usersorder.storeid='" + shopid + "' ");
            }
            //只按照时间查询  
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid == "")
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append(" and orderTime between  '" + datetime1 + "' AND '" + datetime2 + "' ");
            }
            if (!string.IsNullOrEmpty(adminUsername))
            {
                sql.Append(" and `user`.wechatName LIKE  '%" + adminUsername + "%' ");
            }
            if (!string.IsNullOrEmpty(userPhone))
            {
                sql.Append(" and `user`.userPhone LIKE  '%" + userPhone + "%' ");
            }
            //计算总的条数
            count = bll.SearchBySQL<usersorder>(sql.ToString()).Count();

            int start = (page - 1) * rows;//从第几条 开始  rows  取多少条记录

            sql.Append(" ORDER BY orderTime DESC limit " + start + "," + rows + " ");

            var sqlList = bll.SearchBySQL<orderStaicModel>(sql.ToString());
            foreach (var oneOrder in sqlList)
            {
                OrderModel orderModel = new OrderModel();
                orderModel.userCode = oneOrder.userCode;
                var a = bll.Search(x => x.userCode == oneOrder.userCode);
                if (a.Count() >= 2)
                {
                    orderModel.isRepeat = "是";
                }
                else
                {
                    orderModel.isRepeat = "否";
                }
                orderModel.orderCode = oneOrder.orderCode;
                orderModel.orderTime = Convert.ToDateTime(oneOrder.orderTime).ToString();
                orderModel.wechatName = oneOrder.wechatName;
                orderModel.userPhone = oneOrder.userPhone;
                orderModel.orderSumprice = Convert.ToDecimal(oneOrder.orderSumprice);
                orderModel.preferentialMoney = oneOrder.discountMoney.ToString();
                orderModel.orderPay = oneOrder.orderPay == 1 ? "支付宝" : oneOrder.orderPay == 2 ? "微信" : oneOrder.orderPay == 3 ? "优惠券" : "人脸";
                orderModel.storeName = oneOrder.storeName;
                orderModel.consumType = "线下消费";
                orderModel.consumTypeID = 2;
                OrderList.Add(orderModel);
            }
            return Json(new { rows = OrderList, total = count });
        }
      
        public class orderStaicModel
        {
            public int userCode { get; set; }    
            public string userName { get; set; }   
            public string orderCode{ get; set; }
            public string orderTime { get; set; }
            public decimal orderSumprice { get; set; }
            public string wechatName { get; set; }
            public string userPhone { get; set; }
            public int orderPay { get; set; }     //支付方式
            public string discountMoney { get; set; }    //优惠金额
            public string storeName { get; set; }    //消费店铺
        }

    }
}